package com.b2c.repository.erp;

import com.alibaba.fastjson.JSONObject;
import com.b2c.entity.result.PagingResponse;
import com.b2c.common.utils.HttpUtil;
import com.b2c.entity.ErpGoodsStockInfoEntity;
import com.b2c.entity.ErpOrderReturnEntity;
import com.b2c.entity.ErpOrderReturnItemEntity;
import com.b2c.entity.enums.OrderCancelStateEnums;
import com.b2c.entity.result.EnumResultVo;
import com.b2c.entity.result.ResultVo;
import com.b2c.entity.ErpGoodsBadStockEntity;
import com.b2c.entity.ErpStockInFormEntity;
import com.b2c.entity.ErpStockOutFormItemDetailEntity;
import com.b2c.repository.Tables;
import com.b2c.entity.enums.erp.EnumErpSalesOrderRefundStatus;
import com.b2c.entity.enums.erp.EnumGoodsStockLogSourceType;
import com.b2c.entity.enums.erp.EnumGoodsStockLogType;
import com.b2c.entity.enums.erp.EnumOrderReturnStatus;
import com.b2c.entity.vo.ErpOrderReturnItemVo;
import com.b2c.entity.vo.ErpOrderReturnVo;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.transaction.interceptor.TransactionAspectSupport;
import org.springframework.util.StringUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * 描述：
 *
 * @author qlp
 * @date 2019-10-15 15:07
 */
@Repository
public class ErpOrderReturnRepository {
    @Autowired
    private JdbcTemplate jdbcTemplate;

    /**
     * 查询总页数
     *
     * @return
     */
    protected int getTotalSize() {
        return jdbcTemplate.queryForObject("SELECT FOUND_ROWS() as row_num;", int.class);
    }

    /**
     * @param pageIndex
     * @param pageSize
     * @return
     */
    @Transactional
    public PagingResponse<ErpOrderReturnVo> getList(int pageIndex, int pageSize, String refNum, String orderNum,
            String logisticsCode, String refMobile, Integer status) {
        String sql = "SELECT SQL_CALC_FOUND_ROWS o.*,sh.name as shopName,"
                + "(SELECT SUM(quantity) FROM erp_order_return_item WHERE orderId=o.id ) as totalQuantity" + " FROM "
                + Tables.ErpOrderReturn + " AS o " + " LEFT JOIN " + Tables.DcShop + " as sh on sh.id= o.shopId"
                + " WHERE 1=1 ";

        List<Object> params = new ArrayList<>();

        if (StringUtils.isEmpty(refNum) == false) {
            sql += " AND o.order_num=?";
            params.add(refNum);
        }

        if (StringUtils.isEmpty(orderNum) == false) {
            sql += " AND o.source_order_num=?";
            params.add(orderNum);
        }

        if (StringUtils.isEmpty(logisticsCode) == false) {
            sql += " AND o.logisticsCode=?";
            params.add(logisticsCode);
        }

        if (StringUtils.isEmpty(refMobile) == false) {
            sql += " AND o.mobile=?";
            params.add(refMobile);
        }
        if (status != null && status > -1) {
            sql += " AND o.status=?";
            params.add(status);
        }

        sql += " ORDER BY o.id DESC LIMIT ?,?";
        params.add((pageIndex - 1) * pageSize);
        params.add(pageSize);
        var list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(ErpOrderReturnVo.class),
                params.toArray(new Object[params.size()]));
        var totalSize = getTotalSize();
        String itemSQL = "SELECT ori.*,gs.color_value,gs.color_image,gs.size_value,g.number as goodsNumber FROM "
                + Tables.ErpOrderReturnItem + " as ori "
                + " left join erp_goods_spec as gs on gs.id=ori.skuId left join erp_goods g on g.id = ori.goodsId WHERE ori.orderId=?";
        for (var li : list) {
            li.setItems(
                    jdbcTemplate.query(itemSQL, new BeanPropertyRowMapper<>(ErpOrderReturnItemVo.class), li.getId()));
        }

        return new PagingResponse<>(pageIndex, pageSize, totalSize, list);
    }

    /**
     * 获取退货明细
     *
     * @param returnOrderId
     * @return
     */
    public List<ErpOrderReturnItemVo> getItemListById(Long returnOrderId) {
        String sql = "SELECT i.*,gs.color_value,gs.size_value,g.name as goodsName FROM " + Tables.ErpOrderReturnItem
                + " as i " + " LEFT JOIN " + Tables.ErpGoodsSpec + " gs on gs.id=i.skuId " + " LEFT JOIN "
                + Tables.ErpGoods + " g on g.id=i.goodsId " + " WHERE i.orderId=? ";
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(ErpOrderReturnItemVo.class), returnOrderId);
    }

    public ErpOrderReturnEntity getById(Long returnOrderId) {
        try {
            String sql = "SELECT o.*,sh.name as shopName FROM " + Tables.ErpOrderReturn + " as o LEFT JOIN "
                    + Tables.DcShop + " as sh on sh.id=o.shopId WHERE o.id=?";
            return jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(ErpOrderReturnEntity.class),
                    returnOrderId);
        } catch (Exception e) {
            return null;
        }
    }
    /**
     * 确认收货
     *
     * @param returnOrderId
     * @return
     */
    @Transactional
    public ResultVo<Integer> confirmReceiveAndStockIn(Long returnOrderId, String[] returnItemIds, String[] locationIds,
            Integer userId, String userName) {

        /************** 查询数据 判断状态 ***************/
        ErpOrderReturnEntity erpOrderReturn = null;
        try {
            erpOrderReturn = jdbcTemplate.queryForObject("SELECT * FROM " + Tables.ErpOrderReturn + " WHERE id=?",
                    new BeanPropertyRowMapper<>(ErpOrderReturnEntity.class), returnOrderId);

            if (erpOrderReturn.getStatus().intValue() == EnumOrderReturnStatus.WaitSend.getIndex())
                return new ResultVo(EnumResultVo.DataError, "买家还没有发货");
            else if (erpOrderReturn.getStatus().intValue() != EnumOrderReturnStatus.WaitReceive.getIndex())
                return new ResultVo(EnumResultVo.DataError, "退货订单已经处理过了");

            if (erpOrderReturn.getShopId() == null || erpOrderReturn.getShopId() == 0)
                return new ResultVo(EnumResultVo.DataError, "系统数据错误：退货订单找不到shopId");

        } catch (Exception e) {
            return new ResultVo(EnumResultVo.DataError, "系统数据错误：" + e.getMessage());
        }

        try {

            var stockInForm = jdbcTemplate.query(
                    "SELECT * FROM " + Tables.ErpStockInForm + " WHERE invoiceId=? AND inType=2",
                    new BeanPropertyRowMapper<>(ErpStockInFormEntity.class), returnOrderId);
            if (stockInForm != null && stockInForm.size() > 0)
                return new ResultVo(EnumResultVo.DataError, "已经存在退货入库单，无需操作。退货入库单号："+stockInForm.get(0).getId());




/*********** 入库操作 ************/

            /*********** 入库操作一、创建入库表单（以退货单据为主要内容） ************/
            String sql1 = "INSERT INTO " + Tables.ErpStockInForm
                    + " (number,invoiceId,stockInUserId,stockInUserName,stockInTime1,inType) VALUE (?,?,?,?,?,?)";

            KeyHolder keyHolder = new GeneratedKeyHolder();
            ErpOrderReturnEntity finalErpOrderReturn = erpOrderReturn;
            jdbcTemplate.update(new PreparedStatementCreator() {
                @Override
                public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                    PreparedStatement ps = connection.prepareStatement(sql1, Statement.RETURN_GENERATED_KEYS);
                    ps.setString(1, finalErpOrderReturn.getOrderNum());
                    ps.setLong(2, finalErpOrderReturn.getId());
                    ps.setInt(3, userId);
                    ps.setString(4, userName);
                    ps.setLong(5, System.currentTimeMillis() / 1000);
                    ps.setInt(6, 2);
                    return ps;
                }
            }, keyHolder);

            Integer stockInFormId = keyHolder.getKey().intValue();

            /*********** 入库操作一 1、创建入库表单明细（以退货单据为主要内容） ************/
            // 查询退货明细
            // var returnItem = jdbcTemplate.query("SELECT * FROM
            // "+Tables.ErpOrderReturnItem + " WHERE orderId=? ",new
            // BeanPropertyRowMapper<>(ErpOrderReturnItemEntity.class),returnOrderId);

            String sql2 = "INSERT INTO " + Tables.ErpStockInFormItem
                    + " (formId,goodsId,specId,locationId,quantity,itemId,remark) VALUE (?,?,?,?,?,?,?)";

            for (int i = 0; i < returnItemIds.length; i++) {
                // 查找returnItem
                ErpOrderReturnItemEntity returnItem = jdbcTemplate.queryForObject(
                        "SELECT * FROM " + Tables.ErpOrderReturnItem + " WHERE orderId=? AND id=?",
                        new BeanPropertyRowMapper<>(ErpOrderReturnItemEntity.class), returnOrderId,
                        Long.parseLong(returnItemIds[i]));

                /*****************
                 * 利用erp_order_return_item表中的orderItemId查找到订单明细表中的出库批次记录
                 ************************/
/*                if (returnItem.getOrderItemId() == null) {
                    TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
                    return new ResultVo(EnumResultVo.DataError,
                            "数据错误，退货item表中找不到订单orderItemId信息。退货itemId" + returnItem.getId());
                }*/
                //查询orderItemId
/*                if(returnItem.getOrderItemId().longValue()==0){
                    Long orderItemId =jdbcTemplate.queryForObject("SELECT IFNULL((SELECT i.id FROM erp_order_item i LEFT JOIN erp_order e ON i.orderId=e.id WHERE e.order_num=? AND  i.skuId=? LIMIT 1),0) id",Long.class,erpOrderReturn.getSourceOrderNum(),returnItem.getSkuId());
                    if(orderItemId>0){
                        returnItem.setOrderItemId(orderItemId);
                        jdbcTemplate.update("update erp_order_return_item set orderItemId=? where id=?",orderItemId,returnItem.getId());
                    }else {
                        TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
                        return new ResultVo(EnumResultVo.DataError,
                                "数据错误，退货item表中找不到订单orderItemId信息。退货itemId" + returnItem.getId());
                    }
                }*/
/*                ErpOrderItemEntity orderItem = null;
                try {
                    // 查询到对应的orderItem信息
                    orderItem = jdbcTemplate.queryForObject(
                            "SELECT id,stockOutFormId,stockOutFormItemId,skuId FROM " + Tables.ErpOrderItem + " WHERE id=? ",
                            new BeanPropertyRowMapper<>(ErpOrderItemEntity.class), returnItem.getOrderItemId());

                    if (orderItem.getStockOutFormItemId() == null) {
                        TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
                        return new ResultVo(EnumResultVo.DataError,
                                "数据错误，退货对应的orderItem表中找不到出库单itemId信息。退货itemId" + returnItem.getId());
                    }
                    if(orderItem.getStockOutFormItemId().longValue()<=0){
                        Long outFormItemId = jdbcTemplate.queryForObject("SELECT IFNULL((SELECT id from erp_stock_out_form_item WHERE formId=? and itemId=? LIMIT 1),0) id",Long.class,orderItem.getStockOutFormId(),orderItem.getId());
                        if(outFormItemId>0){
                            orderItem.setStockOutFormItemId(outFormItemId);
                            jdbcTemplate.update("update " + Tables.ErpOrderItem +"set stockOutFormItemId=? where id=?",outFormItemId,orderItem.getId());
                        }
                    }
                } catch (Exception E) {
                    TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
                    return new ResultVo(EnumResultVo.DataError, "数据错误，找不到订单item信息。系统异常：" + E.getMessage());
                }*/

                Integer locationId = Integer.parseInt(locationIds[i]);// 当前要入库的仓位


                // 要入库的仓位信息
                ErpGoodsStockInfoEntity stockInfo = jdbcTemplate.queryForObject(
                        "SELECT * FROM " + Tables.ErpGoodsStockInfo
                                + " WHERE  locationId=? AND isDelete = 0 and  (specId=? or specNumber=?)",
                        new BeanPropertyRowMapper<>(ErpGoodsStockInfoEntity.class),locationId,returnItem.getSkuId(),returnItem.getSkuNumber());

                String remarkIn = "";// 入库说明
                List<ErpStockOutFormItemDetailEntity> stockOutFormItemDetail=null;
                if(returnItem.getStockInfoItemId()>0){
                    // 查询到order出库时的出库明细ID
                   /* String sql = "SELECT * FROM erp_stock_out_form_item_detail WHERE stock_out_form_item_id=? order by id asc";
                    stockOutFormItemDetail = jdbcTemplate.query(sql,
                            new BeanPropertyRowMapper(ErpStockOutFormItemDetailEntity.class), orderItem.getStockOutFormItemId());

                    if (stockOutFormItemDetail == null || stockOutFormItemDetail.size() == 0) {
                        TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
                        return new ResultVo(EnumResultVo.DataError, "数据错误，找不到退货对应的订单出库记录信息。退货itemId" + returnItem.getId());
                    }

                    if(stockOutFormItemDetail.size()>0){
                        List<ErpStockOutFormItemDetailEntity> goodsStockInfoItemIdArr = new ArrayList<>();// 需要退回入库的批次信息
                        // 循环仓位明细，并更新仓位明细数据
                        long hasInQty = returnItem.getQuantity().longValue();// 需要退货入库的数量
                        for (var sofid : stockOutFormItemDetail) {
                            if (hasInQty == 0)
                                break;
                            if (sofid.getQuantity() >= hasInQty) {
                                sofid.setQuantity(hasInQty);
                                goodsStockInfoItemIdArr.add(sofid);
                                remarkIn += "{pici:" + sofid.getGoodsStockInfoItemId() + ",qty:" + hasInQty + "}";
                                hasInQty = 0;
                            } else {
                                goodsStockInfoItemIdArr.add(sofid);
                                remarkIn += "{pici:" + sofid.getGoodsStockInfoItemId() + ",qty:" + sofid.getQuantity() + "}";
                                hasInQty = hasInQty - sofid.getQuantity().intValue();
                            }
                        }
                        *//****************** 更新仓库明细库存 ***************//*
                        for (var tmp : goodsStockInfoItemIdArr) {
                            jdbcTemplate.update("UPDATE erp_goods_stock_info_item SET currentQty=currentQty+? WHERE id=? ",
                                    tmp.getQuantity(), tmp.getGoodsStockInfoItemId());
                        }
                    }*/
                    jdbcTemplate.update("UPDATE erp_goods_stock_info_item SET currentQty=currentQty+? WHERE id=? ",returnItem.getQuantity(),returnItem.getStockInfoItemId());
                }

                if(returnItem.getStockInfoItemId() == 0){
                    Long infoItemId = jdbcTemplate.queryForObject("SELECT IFNULL((SELECT id from erp_goods_stock_info_item WHERE stockInfoId=? order by id DESC LIMIT 1 ),0) id",Long.class,stockInfo.getId());
                    if(infoItemId>0){
                        jdbcTemplate.update("UPDATE erp_goods_stock_info_item SET currentQty=currentQty+? WHERE id=? ",returnItem.getQuantity(),infoItemId);
                    }else {
                        jdbcTemplate.update("INSERT INTO erp_goods_stock_info_item  (stockInfoId,currentQty,purPrice,invoiceId,invoiceInfoId,remark) VALUE (?,?,?,?,?,?)", stockInfo.getId(),returnItem.getQuantity(),0,0,0,"退货旧数据查询不到出库明细");
                    }
                }
                // 更新仓位库存
                jdbcTemplate.update("UPDATE " + Tables.ErpGoodsStockInfo + " SET currentQty=currentQty+? WHERE id=?",
                        returnItem.getQuantity(), stockInfo.getId());

                // 更新商品SKU库存
                jdbcTemplate.update("UPDATE " + Tables.ErpGoodsSpec + " SET currentQty=currentQty+? WHERE id=?",
                        returnItem.getQuantity(), returnItem.getSkuId());

                // 更新退货明细 已入库数量
                jdbcTemplate.update("update " + Tables.ErpOrderReturnItem + " set inQuantity=? where id=?",
                        returnItem.getQuantity(), returnItem.getId());

                // 添加入库item
                jdbcTemplate.update(sql2, stockInFormId, returnItem.getGoodsId(), returnItem.getSkuId(), locationId,
                        returnItem.getQuantity(), returnItem.getId(), remarkIn);

                // 添加入库日志
                String remark = "退货合格入库SKU :" + returnItem.getSkuNumber() + "退货单号:" + erpOrderReturn.getOrderNum()
                        + "，退货itemId：" + returnItem.getId();
                jdbcTemplate.update("INSERT INTO " + Tables.ErpGoodsStockLogs + " SET "
                        + "goodsId=?,goodsNumber=?,specId=?,specNumber=?,locationId=?,quantity=?,currQty=?,type=?,sourceType=?,sourceId=?"
                        + ",remark=?,createTime=?,createUserId=?,createUserName=?,createOn=?", returnItem.getGoodsId(),
                        stockInfo.getGoodsNumber(), returnItem.getSkuId(), returnItem.getSkuNumber(), locationId,
                        returnItem.getQuantity(), stockInfo.getCurrentQty() + returnItem.getQuantity(),
                        EnumGoodsStockLogType.IN.getIndex(), EnumGoodsStockLogSourceType.Order_Return_In.getIndex(),
                        returnItem.getId(), remark, new Date(), userId, userName, System.currentTimeMillis() / 1000);
            }

            /********* 更新退货单状态 **********/
            jdbcTemplate.update("UPDATE " + Tables.ErpOrderReturn + " SET receiveTime=?,status=? WHERE id=?",
                    System.currentTimeMillis() / 1000, EnumOrderReturnStatus.Complete.getIndex(),
                    erpOrderReturn.getId());

            /********* 更新退货单item状态 **********/
            jdbcTemplate.update("UPDATE " + Tables.ErpOrderReturnItem + " SET receiveTime=?,status=? WHERE orderId=?",
                    System.currentTimeMillis() / 1000, EnumOrderReturnStatus.Complete.getIndex(),
                    erpOrderReturn.getId());

            /******** 同步状态 **********/
            if (erpOrderReturn.getShopId() == 3 || erpOrderReturn.getShopId() == 4
                    || erpOrderReturn.getShopId() == 11) {
                // 云购平台
                /******* 更新云购退货订单状态：已收货 *********/
                jdbcTemplate.update("update  " + Tables.OrderCancel + "  set state=? where order_cancel_num=? ",
                        OrderCancelStateEnums.Received.getIndex(), erpOrderReturn.getOrderNum());
            } else if (erpOrderReturn.getShopId() == 99) {
                // 外发订单
                /******* 更新外发退货订单状态：已收货 *********/
                jdbcTemplate.update(
                        "update  " + Tables.ErpSalesOrderRefund + "  set status=?,completeTime=? where refundNum=? ",
                        EnumErpSalesOrderRefundStatus.Received.getIndex(), System.currentTimeMillis() / 1000,
                        erpOrderReturn.getOrderNum());
            } else if (erpOrderReturn.getShopId() == 8 || erpOrderReturn.getShopId()==19) {

                JSONObject pobj=new JSONObject();
                pobj.put("rid",erpOrderReturn.getOrderNum());
                HttpUtil.doPostJson("http://oms.huayiyungou.com:8080/douyin_notify/douyin_refund_wms_notify",pobj.toJSONString());

                jdbcTemplate.update("update  dc_douyin_orders_refund  set stockStatus=? where order_id=? ",
                        EnumErpSalesOrderRefundStatus.Received.getIndex(), erpOrderReturn.getOrderNum());
            }

            //TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();

        } catch (Exception e) {
            TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
            return new ResultVo(EnumResultVo.SystemException, "系统异常：" + e.getMessage());
        }
        // TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
        return new ResultVo(EnumResultVo.SUCCESS);

    }

    /**
     * 退货订单入库
     *
     * @param locationId 仓位id
     * @param goodsIds
     * @param specId     规格id
     * @param quantities
     */
    // @Transactional(rollbackFor = Exception.class)
    // public void returnOrderStockIn(Integer locationId, Integer goodsIds, Integer
    // specId, Long quantities, Integer itemId, Integer userId, String userName,
    // String billNo, Long returnId) {
    // if (quantities <= 0) return;
    //
    // //跟进仓位编码查询仓位id
    //// Integer locationId = jdbcTemplate.queryForObject("select id from " +
    // Tables.ErpStockLocation + " where number=? AND isDelete=0", Integer.class,
    // locationNumber);
    //
    //// Integer specId = jdbcTemplate.queryForObject("SELECT id FROM " +
    // Tables.ErpGoodsSpec + " WHERE specNumber=?", Integer.class, specNumber);
    // //查询商品规格
    // var spec = jdbcTemplate.queryForObject("SELECT * FROM " + Tables.ErpGoodsSpec
    // + " WHERE id=?", new BeanPropertyRowMapper<>(ErpGoodsSpecEntity.class),
    // specId);
    //
    // var goods = jdbcTemplate.queryForObject("SELECT * FROM " + Tables.ErpGoods +
    // " WHERE id=?", new BeanPropertyRowMapper<>(ErpGoodsEntity.class), goodsIds);
    //
    //// Long current = jdbcTemplate.queryForObject("SELECT currentQty FROM " +
    // Tables.ErpGoodsStockInfo + " WHERE specNumber=? AND locationId=? AND
    // isDelete=0", long.class, specNumber, locationId);
    //
    //
    // //日志
    // String remark = "退货合格入库SKU :" + spec.getSpecNumber() + "退货单号:" + billNo +
    // "，退货itemId：" + itemId;
    // jdbcTemplate.update("INSERT INTO " + Tables.ErpGoodsStockLogs + " SET " +
    // "goodsId=?,goodsNumber=?,specId=?,specNumber=?,locationId=?,quantity=?,type=?,sourceType=?,sourceId=?"
    // +
    // ",remark=?,createTime=?,createUserId=?,createUserName=?,createOn=?",
    // goodsIds, goods.getNumber(), specId, spec.getSpecNumber(), locationId,
    // quantities,
    // EnumGoodsStockLogType.IN.getIndex(),
    // EnumGoodsStockLogSourceType.Order_Return_In.getIndex(), itemId, remark, new
    // Date(), userId
    // , userName, System.currentTimeMillis() / 1000
    // );
    // //更新退货明细 已入库数量
    // jdbcTemplate.update("update " + Tables.ErpOrderReturnItem + " set
    // inQuantity=inQuantity+? where id=?", quantities, itemId);
    //
    // //更新仓位库存
    // jdbcTemplate.update("UPDATE " + Tables.ErpGoodsStockInfo + " SET
    // currentQty=currentQty+? WHERE locationId=? AND goodsId=? AND specId=?",
    // quantities, locationId, goodsIds, specId);
    //
    // //更新商品SKU库存
    // jdbcTemplate.update("UPDATE " + Tables.ErpGoodsSpec + " SET
    // currentQty=currentQty+? WHERE id=?", quantities, specId);
    //
    //
    // //判断退货数量，数量相等则更新状态
    // Long current1 = jdbcTemplate.queryForObject("SELECT SUM(quantity) FROM " +
    // Tables.ErpOrderReturnItem + " WHERE orderId=?", long.class, returnId);
    // Long current2 = jdbcTemplate.queryForObject("SELECT SUM(inQuantity) FROM " +
    // Tables.ErpOrderReturnItem + " WHERE orderId=?", long.class, returnId);
    // Long current3 = jdbcTemplate.queryForObject("SELECT SUM(badQuantity) FROM " +
    // Tables.ErpOrderReturnItem + " WHERE orderId=?", long.class, returnId);
    // if (current1.longValue() == (current2.longValue() + current3.longValue()))
    // jdbcTemplate.update("UPDATE " + Tables.ErpOrderReturn + " SET status=? WHERE
    // id=?", EnumOrderReturnStatus.Complete.getIndex(), returnId);
    //
    // }

    /**
     * @param id 退货id
     * @return
     */
    public ErpOrderReturnVo getStockInfo(Long id) {
        ErpOrderReturnVo vo = jdbcTemplate.queryForObject(
                "SELECT id,order_num,source_order_num,status FROM erp_order_return WHERE id=? ",
                new BeanPropertyRowMapper<>(ErpOrderReturnVo.class), id);

        StringBuffer sb = new StringBuffer();
        sb.append("SELECT SQL_CALC_FOUND_ROWS A.*,B.`name` goodsName,C.color_value,C.size_value,C.color_image ");
        sb.append("FROM ").append(Tables.ErpOrderReturnItem).append(" A ");
        sb.append("LEFT JOIN ").append(Tables.ErpGoods).append(" B ON B.id=A.goodsId ");
        sb.append("LEFT JOIN ").append(Tables.ErpGoodsSpec).append(" C ON C.id=A.skuId ");
        sb.append("WHERE A.orderId=? ");

        List<ErpOrderReturnItemVo> query = jdbcTemplate.query(sb.toString(),
                new BeanPropertyRowMapper<>(ErpOrderReturnItemVo.class), id);

        String sql = "SELECT *,IFNULL((select number as locationId from erp_stock_location where id=A.locationId ),0) locationName FROM erp_goods_stock_info A WHERE  isDelete=0 and (specId=? or specNumber=? )";
        for (ErpOrderReturnItemVo item : query) {
            item.setItems(jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(ErpGoodsStockInfoEntity.class),
                    item.getSkuId(),item.getSkuNumber()));
        }
        vo.setItems(query);
        return vo;
    }

    /**
     * 检查合格检验仓位是否存在
     *
     * @param specNumber
     * @param locationNmae
     * @return
     */
    public Integer checkStockInfo(String specNumber, String locationNmae) {
        try {
            Integer locationId = jdbcTemplate.queryForObject("select id from erp_stock_location where name=?",
                    Integer.class, locationNmae);
            return jdbcTemplate.queryForObject(
                    "SELECT id FROM erp_goods_stock_info WHERE specNumber=? AND locationId=?", Integer.class,
                    specNumber, locationId);
        } catch (Exception e) {
            return -1;
        }
    }

    public void erp_order_return_cancel(Long erpOrderReturnId) {
      jdbcTemplate.update("update erp_order_return set status=3 where id=?",erpOrderReturnId);
    }

    /**
     * 检查不良品仓位
     * 
     * @param specNumber
     * @param locationNmae
     * @return
     */
    /*
     * public Integer checkBadStock(String specNumber, String locationNmae) { try {
     * Integer locationId =
     * jdbcTemplate.queryForObject("select id from erp_stock_location where name=?",
     * Integer.class, locationNmae); return jdbcTemplate.
     * queryForObject("SELECT id FROM erp_goods_bad_stock WHERE specNumber=? AND locationId=?"
     * , Integer.class, specNumber, locationId); } catch (Exception e) { return -1;
     * } }
     */

    /**
     * 保存不良品检验单
     *
     * @param goodsIds
     * @param specNumber
     * @param quantities
     */
    @Transactional
    public void returnOrderStockBadIn(Integer goodsIds, String specNumber, Long quantities, Integer itemId,
            String billNo, Integer returnId) {
        if (quantities <= 0)
            return;
        // 新增库存
        // Integer locationId = jdbcTemplate.queryForObject("select id from " +
        // Tables.ErpStockLocation + " where name=?", Integer.class, locationName);
        Integer locationId = 0;
        Integer specId = jdbcTemplate.queryForObject("SELECT id FROM " + Tables.ErpGoodsSpec + " WHERE specNumber=?",
                Integer.class, specNumber);
        Long badStockId = 0l;
        try {
            // 查询不良品仓是否存在该商品
            var badStock = jdbcTemplate.queryForObject(
                    "SELECT * FROM " + Tables.ErpGoodsBadStock + " WHERE specId=? AND locationId=?",
                    new BeanPropertyRowMapper<>(ErpGoodsBadStockEntity.class), specId, locationId);
            badStockId = badStock.getId();
            // 有记录，update
            jdbcTemplate.update("UPDATE " + Tables.ErpGoodsBadStock + " set currentQty=currentQty+? WHERE id=?",
                    quantities, badStock.getId());
        } catch (Exception e) {
            String sql = "INSERT INTO " + Tables.ErpGoodsBadStock
                    + " SET currentQty=?,type=2 ,specNumber=? ,locationId=? ,goodsId=? ,specId=?";
            KeyHolder holder = new GeneratedKeyHolder();
            jdbcTemplate.update(new PreparedStatementCreator() {
                @Override
                public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                    PreparedStatement ps = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
                    ps.setLong(1, quantities);
                    ps.setString(2, specNumber);
                    ps.setLong(3, locationId);
                    ps.setInt(4, goodsIds);
                    ps.setInt(5, specId);
                    return ps;
                }
            }, holder);
            badStockId = holder.getKey().longValue();
        }

        // 日志
        String remark = "退货不良品入库SKU :" + specNumber + "退货单号:" + billNo;
        jdbcTemplate.update(
                "INSERT INTO " + Tables.ErpGoodsBadStockLog + " SET "
                        + "stockId=?,specId=?,locationId=?,quantity=?,type=1,remark=?,createTime=?",
                badStockId, specId, locationId, quantities, remark, new Date().getTime() / 1000);

        // 更新退货订单明细已入库数量
        jdbcTemplate.update("UPDATE " + Tables.ErpOrderReturnItem + " SET badQuantity=badQuantity+? where id=? ",
                quantities, itemId);

        // 判读已入库数量，数量相等则更新状态
        Long current1 = jdbcTemplate.queryForObject(
                "SELECT SUM(quantity) FROM " + Tables.ErpOrderReturnItem + " WHERE orderId=?", long.class, returnId);
        Long current2 = jdbcTemplate.queryForObject(
                "SELECT SUM(inQuantity) FROM " + Tables.ErpOrderReturnItem + " WHERE orderId=?", long.class, returnId);
        Long current3 = jdbcTemplate.queryForObject(
                "SELECT SUM(badQuantity) FROM " + Tables.ErpOrderReturnItem + " WHERE orderId=?", long.class, returnId);
        if (current1 == current2 + current3)
            jdbcTemplate.update("UPDATE " + Tables.ErpOrderReturn + " SET status=? WHERE id=?",
                    EnumOrderReturnStatus.Complete.getIndex(), returnId);

    }

    private String queryReturnItemSQL() {
        String sql = "SELECT SQL_CALC_FOUND_ROWS i.*,o.source_order_num,gs.color_value,gs.size_value,g.number as goodsNumber,g.name as goodsName,o.order_num,sh.name as shopName,o.contactPerson,unit.name as unitName "
                + " FROM " + Tables.ErpOrderReturnItem + " as i " + " LEFT JOIN " + Tables.ErpOrderReturn
                + " as o on o.id=i.orderId " + " LEFT JOIN " + Tables.ErpGoodsSpec + " gs on gs.id=i.skuId "
                + " LEFT JOIN " + Tables.ErpGoods + " g on g.id=i.goodsId " + " LEFT JOIN " + Tables.ErpUnit
                + " unit on unit.id=g.unitId " + " LEFT JOIN " + Tables.DcShop + " sh on sh.id=o.shopId "
                + " WHERE 1=1 ";

        return sql;
    }

    /**
     * 获取退货完成的明细list
     * 
     * @param pageIndex
     * @param pageSize
     * @param orderNum
     * @param skuNumber
     * @param startTime
     * @param endTime
     * @return
     */
    @Transactional
    public PagingResponse<ErpOrderReturnItemVo> getReturnCompleteItemList(Integer pageIndex, Integer pageSize,
            String orderNum, String skuNumber, Integer startTime, Integer endTime) {
        String sql = queryReturnItemSQL();
        sql += " AND o.status = ?";
        List<Object> params = new ArrayList<>();
        params.add(EnumOrderReturnStatus.Complete.getIndex());

        if (StringUtils.isEmpty(orderNum) == false) {
            sql += " AND o.order_num = ? ";
            params.add(orderNum);
        }

        if (StringUtils.isEmpty(skuNumber) == false) {
            sql += " AND i.skuNumber = ? ";
            params.add(skuNumber);
        }

        if (startTime != null && startTime > 0) {
            sql += " AND o.receiveTime >= ? ";
            params.add(startTime);
        }

        if (endTime != null && endTime > 0) {
            sql += " AND o.receiveTime <= ? ";
            params.add(endTime);
        }

        sql += " ORDER BY i.id DESC LIMIT ?,?";
        params.add((pageIndex - 1) * pageSize);
        params.add(pageSize);

        var list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(ErpOrderReturnItemVo.class),
                params.toArray(new Object[params.size()]));
        return new PagingResponse<>(pageIndex, pageSize, getTotalSize(), list);
    }

    public List<ErpOrderReturnItemVo> getReturnCompleteItemListForExcel(String orderNum, String skuNumber,
            Integer startTime, Integer endTime) {
        String sql = queryReturnItemSQL();
        sql += " AND o.status = ?";
        List<Object> params = new ArrayList<>();
        params.add(EnumOrderReturnStatus.Complete.getIndex());

        if (StringUtils.isEmpty(orderNum) == false) {
            sql += " AND o.order_num = ? ";
            params.add(orderNum);
        }

        if (StringUtils.isEmpty(skuNumber) == false) {
            sql += " AND i.skuNumber = ? ";
            params.add(skuNumber);
        }

        if (startTime != null && startTime > 0) {
            sql += " AND o.receiveTime >= ? ";
            params.add(startTime);
        }

        if (endTime != null && endTime > 0) {
            sql += " AND o.receiveTime <= ? ";
            params.add(endTime);
        }

        sql += " ORDER BY i.id DESC ";

        var list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(ErpOrderReturnItemVo.class),
                params.toArray(new Object[params.size()]));
        return list;
    }
}
